<!DOCTYPE html>
<html lang="en-US">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>Mysql一些记不住的 | 叫我华仔~</title>
    <meta name="description" content="Write the Code. Change the world">
    <link rel="shortcut icon" type="image/x-icon" href="https://tva1.sinaimg.cn/large/0060lm7Tly1g4lrkxbno3j30go0gogm9.jpg">
    
    <link rel="preload" href="/assets/css/0.styles.cd3776eb.css" as="style"><link rel="preload" href="/assets/js/app.48b22e4d.js" as="script"><link rel="preload" href="/assets/js/2.3fdd559a.js" as="script"><link rel="preload" href="/assets/js/7.f7efdc2b.js" as="script"><link rel="prefetch" href="/assets/js/10.e1dd09fa.js"><link rel="prefetch" href="/assets/js/11.4aed7e88.js"><link rel="prefetch" href="/assets/js/12.82f25f15.js"><link rel="prefetch" href="/assets/js/13.0fee9ac6.js"><link rel="prefetch" href="/assets/js/14.12b766db.js"><link rel="prefetch" href="/assets/js/15.867ab787.js"><link rel="prefetch" href="/assets/js/3.378e0b39.js"><link rel="prefetch" href="/assets/js/4.3e162dee.js"><link rel="prefetch" href="/assets/js/5.3b714238.js"><link rel="prefetch" href="/assets/js/6.80a92dc6.js"><link rel="prefetch" href="/assets/js/8.a13d01b5.js"><link rel="prefetch" href="/assets/js/9.2299158f.js">
    <link rel="stylesheet" href="/assets/css/0.styles.cd3776eb.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><!----> <span class="site-name">叫我华仔~</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/nodejs/JavaScript/JavaScript面试题大全.html" class="nav-link">Nodejs</a></div><div class="nav-item"><a href="/database/mysql/Explain详解.html" class="nav-link">Database</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title">Linux-Deploy</span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/linux-deploy/docker/优化docker打包速度和体积.html" class="nav-link">Docker</a></li></ul></div></div><div class="nav-item"><a href="/tools/Blog推荐.html" class="nav-link">Tools</a></div><div class="nav-item"><a href="/user/me.html" class="nav-link">关于我</a></div><div class="nav-item"><a href="https://github.com/zwhgithub" target="_blank" rel="noopener noreferrer" class="nav-link external">
  Github
  <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/nodejs/JavaScript/JavaScript面试题大全.html" class="nav-link">Nodejs</a></div><div class="nav-item"><a href="/database/mysql/Explain详解.html" class="nav-link">Database</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title">Linux-Deploy</span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/" class="nav-link">Linux</a></li><li class="dropdown-item"><!----> <a href="/linux-deploy/docker/优化docker打包速度和体积.html" class="nav-link">Docker</a></li></ul></div></div><div class="nav-item"><a href="/tools/Blog推荐.html" class="nav-link">Tools</a></div><div class="nav-item"><a href="/user/me.html" class="nav-link">关于我</a></div><div class="nav-item"><a href="https://github.com/zwhgithub" target="_blank" rel="noopener noreferrer" class="nav-link external">
  Github
  <svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg></a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>Mysql</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/database/mysql/Explain详解.html" class="sidebar-link">Explain详解</a></li><li><a href="/database/mysql/Mysql索引最佳攻略.html" class="sidebar-link">Mysql索引最佳攻略</a></li><li><a href="/database/mysql/Mysql一些记不住的.html" class="active sidebar-link">Mysql一些记不住的</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/database/mysql/Mysql一些记不住的.html#用户管理" class="sidebar-link">用户管理</a></li><li class="sidebar-sub-header"><a href="/database/mysql/Mysql一些记不住的.html#mysql权限管理" class="sidebar-link">Mysql权限管理</a></li></ul></li></ul></section></li></ul> </aside> <main class="page"> <div class="theme-default-content content__default"><h2 id="用户管理"><a href="#用户管理" aria-hidden="true" class="header-anchor">#</a> 用户管理</h2> <h4 id="新增账户"><a href="#新增账户" aria-hidden="true" class="header-anchor">#</a> 新增账户</h4> <ul><li>hostxxx：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%。</li> <li>passwordxxx：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。</li></ul> <p>示例：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'jack'</span><span class="token variable">@'localhost'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'123456'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'rose'</span><span class="token variable">@'192.168.38.110_'</span> IDENDIFIED <span class="token keyword">BY</span> <span class="token string">'123456'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'rose'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'dev_code'</span><span class="token variable">@'%'</span> IDENTIFIED <span class="token keyword">BY</span> <span class="token string">'123456'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p><font color="red">创建用户后，该用户只能连接到数据库服务器，但并没有操作该数据库服务器的权限。</font></p> <h4 id="删除用户"><a href="#删除用户" aria-hidden="true" class="header-anchor">#</a> 删除用户</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">DROP</span> <span class="token keyword">USER</span> <span class="token string">'usernamexxx'</span><span class="token variable">@'hostxxx'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h4 id="修改账户密码"><a href="#修改账户密码" aria-hidden="true" class="header-anchor">#</a> 修改账户密码</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">use</span> mysql<span class="token punctuation">;</span> 

<span class="token keyword">update</span> <span class="token keyword">user</span> <span class="token keyword">set</span> password<span class="token operator">=</span>password<span class="token punctuation">(</span><span class="token string">&quot;123&quot;</span><span class="token punctuation">)</span> <span class="token keyword">where</span> <span class="token keyword">user</span><span class="token operator">=</span><span class="token string">&quot;abc&quot;</span><span class="token punctuation">;</span> 

flush <span class="token keyword">privileges</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><h2 id="mysql权限管理"><a href="#mysql权限管理" aria-hidden="true" class="header-anchor">#</a> Mysql权限管理</h2> <h4 id="授予权限"><a href="#授予权限" aria-hidden="true" class="header-anchor">#</a> 授予权限</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">GRANT</span> privilegesxxx <span class="token keyword">ON</span> databasenamexxx<span class="token punctuation">.</span>tablenamexxx <span class="token keyword">TO</span> <span class="token string">'usernamexxx'</span><span class="token variable">@'hostxxx'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>privilegesxxx：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL。</li> <li>databasenamexxx：数据库名。</li> <li>tablenamexxx：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用<em>表示，如</em>.*。</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span><span class="token punctuation">,</span><span class="token keyword">DELETE</span> <span class="token keyword">ON</span> doctor<span class="token punctuation">.</span><span class="token keyword">user</span> <span class="token keyword">TO</span> <span class="token string">'jack'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'jack'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> doctor<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'jack'</span><span class="token variable">@'%'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li><font color="red">授权之后需要用户重连MySQL，才能获取相应的权限。</font></li> <li><font color="red">创建用户后，该用户只能连接到数据库服务器，但并没有操作该数据库服务器的权限。</font></li></ul> <h4 id="撤销用户权限"><a href="#撤销用户权限" aria-hidden="true" class="header-anchor">#</a> 撤销用户权限</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">REVOKE</span> privilege <span class="token keyword">ON</span> databasen<span class="token punctuation">.</span>tablen <span class="token keyword">FROM</span> <span class="token string">'user'</span><span class="token variable">@'host'</span><span class="token punctuation">;</span>
<span class="token comment"># 示例</span>
<span class="token keyword">REVOKE</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token string">'jack'</span><span class="token variable">@'%'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div></div> <footer class="page-edit"><!----> <!----></footer> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/database/mysql/Mysql索引最佳攻略.html" class="prev">
          Mysql索引最佳攻略
        </a></span> <!----></p></div> </main></div><div class="global-ui"></div></div>
    <script src="/assets/js/app.48b22e4d.js" defer></script><script src="/assets/js/2.3fdd559a.js" defer></script><script src="/assets/js/7.f7efdc2b.js" defer></script>
  </body>
</html>
